CREATE FUNCTION get_gridcount(p_liked boolean, p_category integer, p_latitude_coef integer, p_longitude_coef integer) RETURNS TABLE(count bigint, xgrid real, ygrid real)
    LANGUAGE sql ROWS 20000
    AS $$
WITH rcount AS 
(SELECT every(liked) as sliked, floor((longitude+1e-6)*p_longitude_coef) as x, floor((latitude+1e-6)*p_latitude_coef) as y
  FROM topo_emotion
  WHERE category = p_category
  GROUP BY sid, x, y
)
SELECT count(*), x::real / p_longitude_coef::real , y::real / p_latitude_coef::real 
  FROM rcount
  WHERE sliked = p_liked 
  GROUP BY x, y;
$$;

ALTER FUNCTION public.get_gridcount(p_liked boolean, p_category integer, p_latitude_coef integer, p_longitude_coef integer) OWNER TO postgres;

CREATE FUNCTION insert_te(sid bigint, category integer, liked boolean, latitude real, longitude real, accuracy real) RETURNS void
    LANGUAGE sql SECURITY DEFINER
    AS $$INSERT INTO topo_emotion(
            sid, category, liked, longitude, latitude, accuracy)
    VALUES (sid, category::smallint, liked, longitude, latitude, accuracy);$$;


ALTER FUNCTION public.insert_te(sid bigint, category integer, liked boolean, latitude real, longitude real, accuracy real) OWNER TO postgres;


CREATE TABLE topo_emotion (
    sid bigint NOT NULL,
    category smallint NOT NULL,
    liked boolean NOT NULL,
    longitude real NOT NULL,
    latitude real NOT NULL,
    rec_time timestamp with time zone DEFAULT now() NOT NULL,
    accuracy real DEFAULT 1000.0 NOT NULL
);


ALTER TABLE public.topo_emotion OWNER TO postgres;

ALTER TABLE ONLY topo_emotion
    ADD CONSTRAINT topo_emotion_pkey PRIMARY KEY (rec_time);

CREATE INDEX topo_emotion_sid_idx ON topo_emotion USING btree (sid);

